<?php
require_once(dirname(__FILE__) . "/settings.inc.php");

require_once(dirname(__FILE__) . "/functions.php");
require_once(dirname(__FILE__) . "/XmlSerializer.class.php");

/*require_once(dirname(__FILE__) . "/Studentsconn.php");*/

/**
 * This is the main PHP file that process the HTTP parameters, 
 * performs the basic db operations (FIND, INSERT, UPDATE, DELETE) 
 * and then serialize the response in an XML format.
 * 
 * XmlSerializer uses a PEAR xml parser to generate an xml response. 
 * this takes a php array and generates an xml according to the following rules:
 * - the root tag name is called "response"
 * - if the current value is a hash, generate a tagname with the key value, recurse inside
 * - if the current value is an array, generated tags with the default value "row"
 * for example, we have the following array: 
 * 
 * $arr = array(
 * 	"data" => array(
 * 		array("id_pol" => 1, "name_pol" => "name 1"), 
 * 		array("id_pol" => 2, "name_pol" => "name 2") 
 * 	), 
 * 	"metadata" => array(
 * 		"pageNum" => 1, 
 * 		"totalRows" => 345
 * 	)
 * 	
 * )
 * 
 * we will get an xml of the following form
 * 
 * <?xml version="1.0" encoding="ISO-8859-1"?>
 * <response>
 *   <data>
 *     <row>
 *       <id_pol>1</id_pol>
 *       <name_pol>name 1</name_pol>
 *     </row>
 *     <row>
 *       <id_pol>2</id_pol>
 *       <name_pol>name 2</name_pol>
 *     </row>
 *   </data>
 *   <metadata>
 *     <totalRows>345</totalRows>
 *     <pageNum>1</pageNum>
 *   </metadata>
 * </response>
 *
 * Please notice that the generated server side code does not have any 
 * specific authentication mechanism in place.
 */
 
 

/**
 * The filter field. This is the only field that we will do filtering after.
 */
$filter_field = "role";
$likenum = 0;
/**
 * we need to escape the value, so we need to know what it is
 * possible values: text, long, int, double, date, defined
 */
$filter_type = "int";

/**
 * constructs and executes a sql select query against the selected database
 * can take the following parameters:
 * $_REQUEST["orderField"] - the field by which we do the ordering. MUST appear inside $fields. 
 * $_REQUEST["orderValue"] - ASC or DESC. If neither, the default value is ASC
 * $_REQUEST["filter"] - the filter value
 * $_REQUEST["pageNum"] - the page index
 * $_REQUEST["pageSize"] - the page size (number of rows to return)
 * if neither pageNum and pageSize appear, we do a full select, no limit
 * returns : an array of the form
 * array (
 * 		data => array(
 * 			array('field1' => "value1", "field2" => "value2")
 * 			...
 * 		), 
 * 		metadata => array(
 * 			"pageNum" => page_index, 
 * 			"totalRows" => number_of_rows
 * 		)
 * ) 
 */



/**
* Constructs and executes a SQL query to Find all students 
* returns arraycollection 
* @author Johan Billström
* @date	2010-03-09
**/

function findStudGroup(){
	global $conn;
	
	
	$Query = "SELECT persons.id, persons.firstname, persons.lastname, groups.name FROM persons INNER JOIN courses_persons ON persons.id = courses_persons.persons_id INNER JOIN groups_courses ON courses_persons.courses_id = groups_courses.courses_id INNER JOIN groups ON groups_courses.groups_id = groups.id WHERE persons.role LIKE '0' ";
	$Result = mysql_query($Query);
	
	//if we have rows in the table, loop through them and fill the array
	$toret = array();
	while ($row_recordset = mysql_fetch_assoc($Result)) {
		array_push($toret, $row_recordset);
	}
	$rscount = mysql_query("SELECT count(*) AS cnt FROM `persons` WHERE role LIKE '0' "); 
	$row_rscount = mysql_fetch_assoc($rscount);
	$totalrows = (int) $row_rscount["cnt"];
	$pageNum = (int)@$_REQUEST["pageNum"];
	
	
	//create the standard response structure
	$toret = array(
		"data" => $toret, 
		"metadata" => array (
			"totalRows" => $totalrows,
			"pageNum" => $pageNum
		)
	);

	return $toret;
	
} 



/**
* Constructs and executes a SQL query to Find all groups
* returns arraycollection 
* @author Johan Billström
* @date	2010-03-10
**/

function findGroups(){
	global $conn;
	
	
	$Query = "SELECT * FROM groups";
	$Result = mysql_query($Query);
	
	//if we have rows in the table, loop through them and fill the array
	$toret = array();
	while ($row_recordset = mysql_fetch_assoc($Result)) {
		array_push($toret, $row_recordset);
	}
	$rscount = mysql_query("SELECT count(*) AS cnt FROM `groups` "); 
	$row_rscount = mysql_fetch_assoc($rscount);
	$totalrows = (int) $row_rscount["cnt"];
	$pageNum = (int)@$_REQUEST["pageNum"];
	
	
	//create the standard response structure
	$toret = array(
		"data" => $toret, 
		"metadata" => array (
			"totalRows" => $totalrows,
			"pageNum" => $pageNum
		)
	);

	return $toret;
	
} 





/**
* Constructs and executes a SQL query to Delete students 
* and belonging to a group. Returns arraycollection 
* @author Johan Billström
* @date	2010-03-09
**/

function delStudPersonCourse(){
	global $conn;

	// check to see if the record actually exists in the database
	$query_recordset = sprintf("SELECT * FROM `persons` WHERE id = %s",
		GetSQLValueString($_REQUEST["id"], "int")
	);
	$recordset = mysql_query($query_recordset, $conn);
	$num_rows = mysql_num_rows($recordset);

	if ($num_rows > 0) {
		$row_recordset = mysql_fetch_assoc($recordset);
		$query_delete = sprintf("DELETE FROM `persons` WHERE id = %s", 
			GetSQLValueString($row_recordset["id"], "int")
		);
				
		$ok = mysql_query($query_delete);
		
		
		
		if ($ok) {
			// delete went through ok, return OK
			$toret = array(
				"data" => $row_recordset["id"], 
				"metadata" => array()
			);
		} else {
			$toret = array(
				"data" => array("error" => mysql_error()), 
				"metadata" => array()
			);
		}

	} else {
		// no row found, return an error
		$toret = array(
			"data" => array("error" => "No row found"), 
			"metadata" => array()
		);
	}
	
	$Other = sprintf("DELETE FROM `courses_persons` WHERE persons_id = %s", 
			GetSQLValueString($row_recordset["id"], "int")
		);
	$otherresult = mysql_query($Other, $conn);
	
	return $toret;
	
} 




/**
* Constructs and executes a SQL query to add students and belonging group
* courses in relation to a specific Group
* Returns arraycollection 
*
* @author Johan Billström
* @date	2010-03-09
**/

function addStudentWithGroup(){
	global $conn;

	//build and execute the insert query
	$query_insert = sprintf("INSERT INTO persons (firstname,lastname,username,password,role,dob) VALUES (%s,%s,%s,%s,%s,%s)" ,			GetSQLValueString($_REQUEST["firstname"], "text"), # 
			GetSQLValueString($_REQUEST["lastname"], "text"), # 
			GetSQLValueString($_REQUEST["username"], "text"), # 
			GetSQLValueString($_REQUEST["password"], "text"), # 
			GetSQLValueString($_REQUEST["role"], "int"), # 
			GetSQLValueString($_REQUEST["dob"], "text")# 
	);
	$ok = mysql_query($query_insert) or die("First" . mysql_error());
	
	if ($ok) {
		// return the new entry, using the insert id
		$toret = array(
			"data" => array(
				array(
					"id" => mysql_insert_id(), 
					"firstname" => $_REQUEST["firstname"], # 
					"lastname" => $_REQUEST["lastname"], # 
					"username" => $_REQUEST["username"], # 
					"password" => $_REQUEST["password"], # 
					"role" => $_REQUEST["role"], #
					"name" => $_REQUEST["name"], # 
					"dob" => $_REQUEST["dob"]# 
				)
			), 
			"metadata" => array()
		);
	} else {
		// we had an error, return it
		$toret = array(
			"data" => array("error" => mysql_error()), 
			"metadata" => array()
		);
	}
	
	$courses_id = intval($_GET['courses_id']);
	$p_id = mysql_insert_id();
	$Other = "INSERT INTO courses_persons (persons_id, courses_id) VALUES ($p_id, $courses_id)"; 
	
	$otherresult = mysql_query($Other, $conn) or die ("Other" . mysql_error());
	
	return $toret;	
} 


	 
 
function findAll() {
	global $conn, $filter_field, $filter_type;

	/**
	 * the list of fields in the table. We need this to check that the sent value for the ordering is indeed correct.
	 */
	$fields = array('id','firstname','lastname','username','password','role','dob');

	$where = "";
	if (@$_REQUEST['filter'] != "") {
		$where = "WHERE " . $filter_field . " LIKE " . GetSQLValueStringForSelect(@$_REQUEST["filter"], $filter_type);	
	}

	$order = "";
	if (@$_REQUEST["orderField"] != "" && in_array(@$_REQUEST["orderField"], $fields)) {
		$order = "ORDER BY " . @$_REQUEST["orderField"] . " " . (in_array(@$_REQUEST["orderDirection"], array("ASC", "DESC")) ? @$_REQUEST["orderDirection"] : "ASC");
	}
	
	//calculate the number of rows in this table
	$rscount = mysql_query("SELECT count(*) AS cnt FROM `persons` $where"); 
	$row_rscount = mysql_fetch_assoc($rscount);
	$totalrows = (int) $row_rscount["cnt"];
	
	//get the page number, and the page size
	$pageNum = (int)@$_REQUEST["pageNum"];
	$pageSize = (int)@$_REQUEST["pageSize"];
	
	//calculate the start row for the limit clause
	$start = $pageNum * $pageSize;

	//construct the query, using the where and order condition
	$query_recordset = "SELECT id,firstname,lastname,username,password,role,dob FROM `persons` $where $order";
	
	//if we use pagination, add the limit clause
	if ($pageNum >= 0 && $pageSize > 0) {	
		$query_recordset = sprintf("%s LIMIT %d, %d", $query_recordset, $start, $pageSize);
	}

	$recordset = mysql_query($query_recordset, $conn);
	
	//if we have rows in the table, loop through them and fill the array
	$toret = array();
	while ($row_recordset = mysql_fetch_assoc($recordset)) {
		array_push($toret, $row_recordset);
	}
	
	//create the standard response structure
	$toret = array(
		"data" => $toret, 
		"metadata" => array (
			"totalRows" => $totalrows,
			"pageNum" => $pageNum
		)
	);

	return $toret;
}






/**
* Constructs and executes a SQL query to find all
* courses in relation to a specific Group
*
* @author Joakim Jarsäter
* @author Lotta Wasston
* @date	2010-03-10
* @return array(
*				data => array (
*					id 			=> 	1 
*			      	firstname 	=> 	flex
*			      	lastname	=> 	2010-03-04
*				)
*				metadata array (
*					info	=> Listar alla Students som tillhör en Course
*				)
*			)	
*
*/
function findStudentAttendingCourse()
{
	global $conn;
	
	$id = intval($_GET['id']);
	
	$recordset = mysql_query("SELECT persons.id,persons.firstname,persons.lastname, SPACE(250) as attended
								FROM courses_persons, persons
								WHERE  persons.id = courses_persons.persons_id
								AND persons.role = 0
								AND courses_persons.courses_id = $id", $conn) or die (mysql_error());
	
	$toret = array();
	while($row = mysql_fetch_assoc($recordset))
	{
		$row['attended'] = true;
		array_push($toret, $row);
	}
	
	$toret = array(
		"data" => $toret,
		"metadata" => array(
			"Info" => "Alla studenter för vald kurs"
			)
		);
	
	return $toret;
}

/**
 * constructs and executes a sql count query against the selected database
 * can take the following parameters:
 * $_REQUEST["filter"] - the filter value
 * returns : an array of the form
 * array (
 * 		data => number_of_rows, 
 * 		metadata => array()
 * ) 
 */
function rowCount() {
	global $conn, $filter_field, $filter_type;

	$where = "";
	if (@$_REQUEST['filter'] != "") {
		$where = "WHERE " . $filter_field . " LIKE " . GetSQLValueStringForSelect(@$_REQUEST["filter"], $filter_type);	
	}

	//calculate the number of rows in this table
	$rscount = mysql_query("SELECT count(*) AS cnt FROM `persons` $where"); 
	$row_rscount = mysql_fetch_assoc($rscount);
	$totalrows = (int) $row_rscount["cnt"];
	
	//create the standard response structure
	$toret = array(
		"data" => $totalrows, 
		"metadata" => array()
	);

	return $toret;
}

/**
 * constructs and executes a sql insert query against the selected database
 * can take the following parameters:
 * $_REQUEST["field_name"] - the list of fields which appear here will be used as values for insert. 
 * If a field does not appear, null will be used.  
 * returns : an array of the form
 * array (
 * 		data => array(
 * 			"primary key" => primary_key_value, 
 * 			"field1" => "value1"
 * 			...
 * 		), 
 * 		metadata => array()
 * ) 
 */
function insert() {
	global $conn;

	//build and execute the insert query
	$query_insert = sprintf("INSERT INTO `persons` (firstname,lastname,username,password,role,dob) VALUES (%s,%s,%s,%s,%s,%s)" ,			GetSQLValueString($_REQUEST["firstname"], "text"), # 
			GetSQLValueString($_REQUEST["lastname"], "text"), # 
			GetSQLValueString($_REQUEST["username"], "text"), # 
			GetSQLValueString($_REQUEST["password"], "text"), # 
			GetSQLValueString($_REQUEST["role"], "int"), # 
			GetSQLValueString($_REQUEST["dob"], "text")# 
	);
	$ok = mysql_query($query_insert);
	
	if ($ok) {
		// return the new entry, using the insert id
		$toret = array(
			"data" => array(
				array(
					"id" => mysql_insert_id(), 
					"firstname" => $_REQUEST["firstname"], # 
					"lastname" => $_REQUEST["lastname"], # 
					"username" => $_REQUEST["username"], # 
					"password" => $_REQUEST["password"], # 
					"role" => $_REQUEST["role"], # 
					"dob" => $_REQUEST["dob"]# 
				)
			), 
			"metadata" => array()
		);
	} else {
		// we had an error, return it
		$toret = array(
			"data" => array("error" => mysql_error()), 
			"metadata" => array()
		);
	}
	return $toret;
}

/**
 * constructs and executes a sql update query against the selected database
 * can take the following parameters:
 * $_REQUEST[primary_key] - thethe value of the primary key
 * $_REQUEST[field_name] - the list of fields which appear here will be used as values for update. 
 * If a field does not appear, null will be used.  
 * returns : an array of the form
 * array (
 * 		data => array(
 * 			"primary key" => primary_key_value, 
 * 			"field1" => "value1"
 * 			...
 * 		), 
 * 		metadata => array()
 * ) 
 */
function update() {
	global $conn;

	// check to see if the record actually exists in the database
	$query_recordset = sprintf("SELECT * FROM `persons` WHERE id = %s",
		GetSQLValueString($_REQUEST["id"], "int")
	);
	$recordset = mysql_query($query_recordset, $conn);
	$num_rows = mysql_num_rows($recordset);
	
	if ($num_rows > 0) {

		// build and execute the update query
		$row_recordset = mysql_fetch_assoc($recordset);
		$query_update = sprintf("UPDATE `persons` SET firstname = %s,lastname = %s,username = %s,password = %s,role = %s,dob = %s WHERE id = %s", 
			GetSQLValueString($_REQUEST["firstname"], "text"), 
			GetSQLValueString($_REQUEST["lastname"], "text"), 
			GetSQLValueString($_REQUEST["username"], "text"), 
			GetSQLValueString($_REQUEST["password"], "text"), 
			GetSQLValueString($_REQUEST["role"], "int"), 
			GetSQLValueString($_REQUEST["dob"], "text"), 
			GetSQLValueString($row_recordset["id"], "int")
		);
		$ok = mysql_query($query_update);
		if ($ok) {
			// return the updated entry
			$toret = array(
				"data" => array(
					array(
						"id" => $row_recordset["id"], 
						"firstname" => $_REQUEST["firstname"], #
						"lastname" => $_REQUEST["lastname"], #
						"username" => $_REQUEST["username"], #
						"password" => $_REQUEST["password"], #
						"role" => $_REQUEST["role"], #
						"dob" => $_REQUEST["dob"]#
					)
				), 
				"metadata" => array()
			);
		} else {
			// an update error, return it
			$toret = array(
				"data" => array("error" => mysql_error()), 
				"metadata" => array()
			);
		}
	} else {
		$toret = array(
			"data" => array("error" => "No row found"), 
			"metadata" => array()
		);
	}
	return $toret;
}

/**
 * constructs and executes a sql update query against the selected database
 * can take the following parameters:
 * $_REQUEST[primary_key] - thethe value of the primary key
 * returns : an array of the form
 * array (
 * 		data => deleted_row_primary_key_value, 
 * 		metadata => array()
 * ) 
 */
function delete() {
	global $conn;

	// check to see if the record actually exists in the database
	$query_recordset = sprintf("SELECT * FROM `persons` WHERE id = %s",
		GetSQLValueString($_REQUEST["id"], "int")
	);
	$recordset = mysql_query($query_recordset, $conn);
	$num_rows = mysql_num_rows($recordset);

	if ($num_rows > 0) {
		$row_recordset = mysql_fetch_assoc($recordset);
		$query_delete = sprintf("DELETE FROM `persons` WHERE id = %s", 
			GetSQLValueString($row_recordset["id"], "int")
		);
		$ok = mysql_query($query_delete);
		if ($ok) {
			// delete went through ok, return OK
			$toret = array(
				"data" => $row_recordset["id"], 
				"metadata" => array()
			);
		} else {
			$toret = array(
				"data" => array("error" => mysql_error()), 
				"metadata" => array()
			);
		}

	} else {
		// no row found, return an error
		$toret = array(
			"data" => array("error" => "No row found"), 
			"metadata" => array()
		);
	}
	return $toret;
}

/**
 * we use this as an error response, if we do not receive a correct method
 * 
 */
$ret = array(
	"data" => array("error" => "No operation"), 
	"metadata" => array()
);

/**
 * check for the database connection 
 * 
 * 
 */
if ($conn === false) {
	$ret = array(
		"data" => array("error" => "database connection error, please check your settings !"), 
		"metadata" => array()
	);
} else {
	mysql_select_db($database_conn, $conn);
	/**
	 * simple dispatcher. The $_REQUEST["method"] parameter selects the operation to execute. 
	 * must be one of the values findAll, insert, update, delete, Count
	 */
	// execute the necessary function, according to the operation code in the post variables
	switch (@$_REQUEST["method"]) {
		case "FindAll":
			$ret = findAll();
		break;
		case "Insert": 
			$ret = insert();
		break;
		case "Update": 
			$ret = update();
		break;
		case "Delete": 
			$ret = delete();
		break;
		case "Count":
			$ret = rowCount();
		break;
		case "FindStudGroup": 
			$ret = findStudGroup();
		break;
		case "FindStudentAttendingCourse": 
			$ret = findStudentAttendingCourse();
		break;
		case "FindGroups": 
			$ret = findGroups();
		break;
		case "DelStudPersonCourse": 
			$ret = delStudPersonCourse();
		break;
		case "AddStudentWithGroup":
			$ret = addStudentWithGroup();
		break;
		
	
	}
}


$serializer = new XmlSerializer();
echo $serializer->serialize($ret);
die();
?>
